﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SCFHS.Data;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using System.Globalization;

namespace SCFHS.Code
{
    public class Controller
    {
        #region Definitions
        public string ConStr
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["mainConStr"].ConnectionString;
            }
        }

        List<MySqlParameter> dbParams = new List<MySqlParameter>();
        //Dictionary<string, object> user = null;
        Order order = null;
        List<SCFHS.Data.User> users = new List<SCFHS.Data.User>();
        DataSet ds; DataTable dt;
        MySqlParameter param;

        #region Tables
        const string tbCities = "dif_Cities";
        const string tbNationalities = "dif_Nationalities";
        const string tbUsers = "dif_users";
        const string tbSummery = "order_summerypoints";
        const string tbRequest = "order_requestpoints";
        const string tbAttachmentFiles = "order_attachments";
        const string tbAttachmentPoints = "order_attachmentspoints";
        const string tbOrder = "order_main";
        const string tbInstruction = "order_instructions";
        const string tbFollowUp = "order_followup";
        #endregion

        #region Sessions
        public string userSession = "SysUser";
        #endregion
        #endregion

        #region Order

        internal int CreateOrder(string personName, string orderNumber, DateTime creationDate, int creatorID)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?_personName", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = personName;
                dbParams.Add(param);

                param = new MySqlParameter("?_orderNumber", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = orderNumber;
                dbParams.Add(param);

                param = new MySqlParameter("?_creationDate", MySqlDbType.Date);
                param.Direction = ParameterDirection.Input;
                param.Value = creationDate;
                dbParams.Add(param);

                param = new MySqlParameter("?_creatorID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = creatorID;
                dbParams.Add(param);

                param = new MySqlParameter("?_orderState", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = (int)OrderStates.Create;
                dbParams.Add(param);

                object Result = SCFHS.Data.MySqlHelper.ExecuteScalar(ConStr, "", "CreateNewOrder", dbParams.ToArray());
                if (Result == null)
                    return 0;

                return int.Parse(Result.ToString());
            }
            catch (Exception)
            {

                throw;
            }
        }

        internal bool UpdateMainOrder(int orderID, string personName, string orderNumber, DateTime creationDate)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?orderID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = orderID;
                dbParams.Add(param);

                param = new MySqlParameter("?_personName", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = personName;
                dbParams.Add(param);

                param = new MySqlParameter("?_orderNumber", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = orderNumber;
                dbParams.Add(param);

                param = new MySqlParameter("?_creationDate", MySqlDbType.Date);
                param.Direction = ParameterDirection.Input;
                param.Value = creationDate;
                dbParams.Add(param);

                int Result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, "", "UpdateMainOrder", dbParams.ToArray());

                if (Result >= 1)
                    return true;
                return false;
            }
            catch (Exception)
            {

                throw;
            }
        }

        internal bool SaveMainOrder(int orderID, int userID)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?orderID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = orderID;
                dbParams.Add(param);

                param = new MySqlParameter("?_orderState", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = (int)OrderStates.ReadyTocomplete;
                dbParams.Add(param);

                param = new MySqlParameter("?userID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = userID;
                dbParams.Add(param);

                param = new MySqlParameter("?_isClosed", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = 0;
                dbParams.Add(param);

                int Result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, "", "UpdateOrderState", dbParams.ToArray());

                if (Result >= 1)
                    return true;
                return false;

            }
            catch (Exception)
            {

                throw;
            }
        }

        internal int PlaceOrder(int orderID, string orderSubject, string orderClassificationNumber, int nationalityID, int birthPlace, int currentResidence, int highSchoolPlace, string idNumber, int idType, string workPlace, int workType)
        {
            dbParams = new List<MySqlParameter>();
            int outResult = 0;

            param = new MySqlParameter("?orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            param = new MySqlParameter("?_orderSubject", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = orderSubject;
            dbParams.Add(param);

            param = new MySqlParameter("?_orderClassificationNumber", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = orderClassificationNumber;
            dbParams.Add(param);

            param = new MySqlParameter("?_nationalityID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = nationalityID;
            dbParams.Add(param);

            param = new MySqlParameter("?_birthPlace", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = birthPlace;
            dbParams.Add(param);

            param = new MySqlParameter("?_currentResidence", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = currentResidence;
            dbParams.Add(param);

            param = new MySqlParameter("?_highSchoolPlace", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = highSchoolPlace;
            dbParams.Add(param);

            param = new MySqlParameter("?_idNumber", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = idNumber;
            dbParams.Add(param);

            param = new MySqlParameter("?_idType", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = idType;
            dbParams.Add(param);

            param = new MySqlParameter("?_workPlace", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = workPlace;
            dbParams.Add(param);

            param = new MySqlParameter("?_workType", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = workType;
            dbParams.Add(param);

            param = new MySqlParameter("?_userID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = ((Data.User)HttpContext.Current.User).userID;
            dbParams.Add(param);

            try
            {
                // Place the order
                outResult = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, "", "PlaceOrder", dbParams.ToArray());
                if (outResult <= 0)
                    return 0;

                // Add Summery points


                //Add Request points

                return 1;
            }
            catch (Exception)
            {
                throw;
            }
        }

        internal Order SelectMainOrderInfo(string orderNumber)
        {
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?_orderNumber", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = orderNumber;
            dbParams.Add(param);

            try
            {
                ds = new DataSet();
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, "", "SelectOrderInfo", dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;
                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return ConvertToOrder(dt);
            }
            catch (Exception)
            {
                throw;
            }

        }

        internal Order SelectFullOrderInfo(int orderID, bool attachmentFiles = false, bool attachmentPoints = false, bool summeryPoints = false, bool requestPoints = false)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbOrder, CommandActions.SELECT.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;

                order = ConvertToOrder(dt);

                if (attachmentFiles)
                    order.orderAttachmentFiles = ListAttachmentFiles(orderID, AttachmentsType.OrderAttachment);
                if (attachmentPoints)
                    order.orderAttachmentPoints = ListAttachmentPoints(orderID);
                if (summeryPoints)
                    order.orderSummeryPoints = ListFullSummeryPointsForOrder(orderID);
                if (requestPoints)
                    order.orderRequestPoints = ListFullRequestPointsForOrder(orderID);
                return order;
            }
            catch (Exception)
            {

                throw;
            }
        }

        internal DataTable SelectFullOrderDetailsInfo(int orderID)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbOrder, CommandActions.SELECTALL.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }

        internal DataTable SelectFullOrderDetailsInfo(string orderNumber)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?_orderNumber", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = orderNumber;
            dbParams.Add(param);
            try
            {

                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, "", "SelectOrderDetailsByNumber", dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }

        internal DataTable SelectOrderByState(int orderState)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?_orderState", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderState;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, "", "SelectOrderByState", dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }

        internal DataTable SelectOrdersByClassificationNumber(string classificationNumber)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?_classificationNumber", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = classificationNumber;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, "", "SelectOrderByClassNumber", dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {

                throw;
            }

        }
        internal DataTable SelectOrdersByIDNumber(string IDNumber)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?_IDNumber", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = IDNumber;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, "", "SelectOrderByIDNumber", dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {

                throw;
            }

        }
        #region Search
        internal DataTable SearchOnOrder(string SearchKey)
        {
            string sqlSearch = "SELECT * FROM " + tbOrder + " WHERE {0}";
            sqlSearch = string.Format(sqlSearch, SearchKey);
            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, sqlSearch);
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion

        #region Summery

        internal bool CreateNewSummeryPoint(int orderID, string point)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?_point", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = point;
                dbParams.Add(param);

                param = new MySqlParameter("?orderID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = orderID;
                dbParams.Add(param);

                int outResult = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbSummery, CommandActions.INSERT.ToString(), dbParams.ToArray());

                if (outResult >= 1)
                    return true;
                return false;
            }
            catch (Exception)
            {
                throw;
            }
        }

        internal bool UpdateSummeryPoint(int summeryPointID, string summeryPoint)
        {
            dbParams = new List<MySqlParameter>();
            MySqlParameter param = new MySqlParameter("?summeryID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = summeryPointID;
            dbParams.Add(param);

            param = new MySqlParameter("?_point", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = summeryPoint;
            dbParams.Add(param);

            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbSummery, CommandActions.UPDATE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        internal bool DeleteSummeryPoint(int summeryPointID)
        {
            param = new MySqlParameter("?summeryID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = summeryPointID;
            dbParams.Add(param);

            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbSummery, CommandActions.DELETE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        //internal DataTable ListSummeryPoint(int orderID) { return new DataTable(); }

        internal DataTable ListFullSummeryPointsForOrder(int orderID)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbSummery, CommandActions.SELECTALL.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {

                throw;
            }

        }

        #endregion

        #region Request
        internal bool CreateNewRequestPoint(int orderID, string point)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?_point", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = point;
                dbParams.Add(param);

                param = new MySqlParameter("?orderID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = orderID;
                dbParams.Add(param);

                int outResult = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbRequest, CommandActions.INSERT.ToString(), dbParams.ToArray());

                if (outResult >= 1)
                    return true;
                return false;
            }
            catch (Exception)
            {
                throw;
            }
        }

        internal bool UpdateRequestPoint(int requestPointID, string requestPoint)
        {
            dbParams = new List<MySqlParameter>();
            MySqlParameter param = new MySqlParameter("?requestID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = requestPointID;
            dbParams.Add(param);

            param = new MySqlParameter("?_point", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = requestPoint;
            dbParams.Add(param);

            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbRequest, CommandActions.UPDATE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        internal bool DeleteRequestPoint(int requestPointID)
        {
            param = new MySqlParameter("?requestID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = requestPointID;
            dbParams.Add(param);

            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbRequest, CommandActions.DELETE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        //internal DataTable ListRequestPoint() { }

        internal DataTable ListFullRequestPointsForOrder(int orderID)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbRequest, CommandActions.SELECTALL.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion

        #region Attachments
        #region Attachments Files
        internal bool AddAttachmentToOrder(int orderID, string attachmentName, int attachmentType, int userID)
        {
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?_orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            param = new MySqlParameter("?_attachmentName", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = attachmentName;
            dbParams.Add(param);

            param = new MySqlParameter("?_attachmentType", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = attachmentType;
            dbParams.Add(param);

            param = new MySqlParameter("?_userID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = userID;
            dbParams.Add(param);

            try
            {
                int outResult = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbAttachmentFiles, CommandActions.INSERT.ToString(), dbParams.ToArray());
                if (outResult >= 1)
                    return true;
                return false;
            }
            catch (Exception)
            {
                throw;
            }
        }
        internal DataTable ListAttachmentFiles(int orderID, AttachmentsType type)
        {
            ds = new DataSet();
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?orderID", MySqlDbType.UInt16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            param = new MySqlParameter("?_attachmentType", MySqlDbType.UInt16);
            param.Direction = ParameterDirection.Input;
            param.Value = (int)type;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbAttachmentFiles, CommandActions.SELECT.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;
                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;

                return dt;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal bool DeleteAttachmentFiles(int attachmentID)
        {
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?attachmentID", MySqlDbType.UInt16);
            param.Direction = ParameterDirection.Input;
            param.Value = attachmentID;
            dbParams.Add(param);

            try
            {

                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbAttachmentFiles, CommandActions.DELETE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #region Attachment Descriptions
        internal bool CreateNewAttachmentPoint(int orderID, string attachmentPoint)
        {
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);


            param = new MySqlParameter("?_attachmentPoint", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = attachmentPoint;
            dbParams.Add(param);


            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbAttachmentPoints, CommandActions.INSERT.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal bool UpdateAttachmentPoint(int attachmentID, string attachmentPoint)
        {
            dbParams = new List<MySqlParameter>();
            param = new MySqlParameter("?attachmentID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = attachmentID;
            dbParams.Add(param);

            param = new MySqlParameter("?_attachmentPoint", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = attachmentPoint;
            dbParams.Add(param);
            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbAttachmentPoints, CommandActions.UPDATE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal bool DeleteAttachmentPoint(int attachmentID)
        {
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?attachmentID", MySqlDbType.UInt16);
            param.Direction = ParameterDirection.Input;
            param.Value = attachmentID;
            dbParams.Add(param);

            try
            {

                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbAttachmentPoints, CommandActions.DELETE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal DataTable ListAttachmentPoints(int orderID)
        {
            ds = new DataSet();
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?orderID", MySqlDbType.UInt16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbAttachmentPoints, CommandActions.SELECTALL.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;
                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;

                return dt;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #endregion

        #region Instructions

        internal bool AddOrderInstruction(Instructions instruction)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?orderID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = instruction.orderID;
                dbParams.Add(param);

                param = new MySqlParameter("?_authorityOf", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = instruction.instructionAuthority;
                dbParams.Add(param);

                param = new MySqlParameter("?_instruction", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = instruction.instruction;
                dbParams.Add(param);

                param = new MySqlParameter("?_transferTo", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = instruction.transferTo;
                dbParams.Add(param);

                param = new MySqlParameter("?_transferReason", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = instruction.transferReason;
                dbParams.Add(param);

                param = new MySqlParameter("?_submitTimestamp", MySqlDbType.Date);
                param.Direction = ParameterDirection.Input;
                param.Value = instruction.SubmitTimestamp;
                dbParams.Add(param);

                param = new MySqlParameter("?_isFinalInstruction", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = Convert.ToInt16(instruction.isFinalInstruction);
                dbParams.Add(param);

                int outResult = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbInstruction, CommandActions.INSERT.ToString(), dbParams.ToArray());

                if (outResult >= 1)
                    return true;
                return false;
            }
            catch (Exception)
            {
                throw;
            }
        }
        internal DataTable ListInstructionsForOrder(int orderID)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbInstruction, CommandActions.SELECT.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return dt;
            }
            catch (Exception)
            {
                throw;
            }

        }

        #endregion

        #region FollowUp

        internal bool AddOrderFollowUp(Followup followup)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?orderID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = followup.orderID;
                dbParams.Add(param);

                param = new MySqlParameter("?_followupAuthority", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = followup.followupAuth;
                dbParams.Add(param);

                param = new MySqlParameter("?_followupSequenceNumber", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = followup.followupSequenceNumber;
                dbParams.Add(param);

                param = new MySqlParameter("?_followupDiscription", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = followup.followupDiscription;
                dbParams.Add(param);

                param = new MySqlParameter("?_followupDate", MySqlDbType.Date);
                param.Direction = ParameterDirection.Input;
                param.Value = followup.followupTimestamp;
                dbParams.Add(param);

                param = new MySqlParameter("?_followerName", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = followup.followupFollowerName;
                dbParams.Add(param);

                int outResult = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbFollowUp, CommandActions.INSERT.ToString(), dbParams.ToArray());

                if (outResult >= 1)
                    return true;
                return false;
            }
            catch (Exception)
            {
                throw;
            }
        }
        internal DataTable ListFollowupsForOrder(int orderID)
        {
            dbParams = new List<MySqlParameter>();
            ds = new DataSet();
            param = new MySqlParameter("?orderID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = orderID;
            dbParams.Add(param);

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbFollowUp, CommandActions.SELECT.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;

                dt = ds.Tables[0];
                //if (dt.Rows.Count < 1)
                //    return null;
                return dt;
            }
            catch (Exception)
            {
                throw;
            }

        }

        #endregion
        #endregion

        #region User Management

        internal SCFHS.Data.User GetCredentials(string userName)
        {

            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?_userName", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = userName;
                dbParams.Add(param);
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, "", "GetCredentials", dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;
                dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                return ConvertToUser(dt.Rows[0]);
            }
            catch (Exception)
            {
                throw;
            }

        }

        internal bool CreateNewUser(string userName, string userPassword, string userDefaultPage, string userProfileName, bool isAdmin, bool isActive, string userRole)
        {
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?_userName", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = userName;
            dbParams.Add(param);

            param = new MySqlParameter("?_userPassword", MySqlDbType.VarChar);
            param.Value = userPassword;
            dbParams.Add(param);

            param = new MySqlParameter("?_userProfileName", MySqlDbType.VarChar);
            param.Value = userProfileName;
            dbParams.Add(param);


            param = new MySqlParameter("?_userDefaultPage", MySqlDbType.VarChar);
            param.Value = userDefaultPage;
            dbParams.Add(param);

            param = new MySqlParameter("?_isAdmin", MySqlDbType.Int16);
            param.Value = Convert.ToInt16(isAdmin);
            dbParams.Add(param);

            param = new MySqlParameter("?_isActive", MySqlDbType.Int16);
            param.Value = Convert.ToInt16(isActive);
            dbParams.Add(param);

            param = new MySqlParameter("?_userRole", MySqlDbType.VarChar);
            param.Value = userRole;
            dbParams.Add(param);

            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbUsers, CommandActions.INSERT.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;

            }
            catch (Exception ex)
            {
                throw;
            }
        }

        internal bool UpdateUser(int userID, string userName, string userPassword, string userDefaultPage, string userProfileName, bool? isAdmin, bool? isActive, string userRole)
        {
            dbParams = new List<MySqlParameter>();

            param = new MySqlParameter("?userID", MySqlDbType.Int16);
            param.Value = userID;
            param.Direction = ParameterDirection.Input;
            dbParams.Add(param);

            param = new MySqlParameter("?_userName", MySqlDbType.VarChar);
            param.Value = userName;
            param.Direction = ParameterDirection.Input;
            dbParams.Add(param);



            param = new MySqlParameter("?_userPassword", MySqlDbType.VarChar);
            param.Value = userPassword;
            param.Direction = ParameterDirection.Input;
            dbParams.Add(param);

            param = new MySqlParameter("?_userRealName", MySqlDbType.VarChar);
            param.Value = userProfileName;
            param.Direction = ParameterDirection.Input;
            dbParams.Add(param);

            param = new MySqlParameter("?_isAdmin", MySqlDbType.Int16);
            param.Value = Convert.ToInt16(isAdmin.Value);
            param.Direction = ParameterDirection.Input;
            dbParams.Add(param);

            param = new MySqlParameter("?_isActive", MySqlDbType.Int16);
            param.Value = Convert.ToInt16(isActive.Value);
            param.Direction = ParameterDirection.Input;
            dbParams.Add(param);

            param = new MySqlParameter("?_userRole", MySqlDbType.VarChar);
            param.Value = userRole;
            dbParams.Add(param);

            int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbUsers, CommandActions.UPDATE.ToString(), dbParams.ToArray());
            if (result >= 1)
                return true;
            return false;
        }

        internal bool UpdateUserMainInfo(int userID, string userName, string userDefaultPage, string userProfileName, bool isAdmin, bool isActive, string userRole)
        {

            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?userID", MySqlDbType.Int16);
                param.Value = userID;
                param.Direction = ParameterDirection.Input;
                dbParams.Add(param);


                param = new MySqlParameter("?_userName", MySqlDbType.VarChar);
                param.Value = userName;
                param.Direction = ParameterDirection.Input;
                dbParams.Add(param);

                param = new MySqlParameter("?_userDefaultPage", MySqlDbType.VarChar);
                param.Value = userDefaultPage;
                param.Direction = ParameterDirection.Input;
                dbParams.Add(param);

                param = new MySqlParameter("?_userProfileName", MySqlDbType.VarChar);
                param.Value = userProfileName;
                param.Direction = ParameterDirection.Input;
                dbParams.Add(param);

                param = new MySqlParameter("?_isAdmin", MySqlDbType.Int16);
                param.Value = Convert.ToInt16(isAdmin);
                param.Direction = ParameterDirection.Input;
                dbParams.Add(param);

                param = new MySqlParameter("?_isActive", MySqlDbType.Int16);
                param.Value = Convert.ToInt16(isActive);
                param.Direction = ParameterDirection.Input;
                dbParams.Add(param);

                param = new MySqlParameter("?_userRole", MySqlDbType.VarChar);
                param.Value = userRole;
                dbParams.Add(param);

                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, "", "UpdateMainUserInfo", dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception)
            {

                throw;
            }
        }

        internal bool UpdateUserPassword(int userID, string userPassword)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?userID", MySqlDbType.Int16);
                param.Value = userID;
                param.Direction = ParameterDirection.Input;
                dbParams.Add(param);

                param = new MySqlParameter("?_userPassword", MySqlDbType.VarChar);
                param.Value = userPassword;
                param.Direction = ParameterDirection.Input;
                dbParams.Add(param);

                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, "", "UpdateUserPassword", dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception)
            {
                throw;
            }
        }

        internal bool DeleteUser(int userID)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                MySqlParameter param = new MySqlParameter("?userID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = userID;
                dbParams.Add(param);
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbUsers, CommandActions.DELETE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        internal List<SCFHS.Data.User> GetUserByName(string userProfileName)
        {
            DataSet ds;
            dbParams = new List<MySqlParameter>();

            try
            {
                MySqlParameter param = new MySqlParameter("?_userProfileName", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = userProfileName;
                dbParams.Add(param);
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbUsers, CommandActions.SELECT.ToString(), dbParams.ToArray());
                if (ds == null || ds.Tables.Count < 1)
                    return null;
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                users = new List<SCFHS.Data.User>();
                foreach (DataRow dr in dt.Rows)
                {
                    users.Add(ConvertToUser(dr));
                }
                return users;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        internal List<SCFHS.Data.User> GetAllUsers()
        {

            users = new List<SCFHS.Data.User>();
            DataSet ds;

            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbUsers, CommandActions.SELECT.ToString());
                if (ds == null || ds.Tables.Count < 1)
                    return null;
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count < 1)
                    return null;
                foreach (DataRow dr in dt.Rows)
                {
                    users.Add(ConvertToUser(dr));
                }
                return users;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        #endregion

        #region System Definitions

        #region Cities
        internal bool AddNewCity(string cityName)
        {
            dbParams = new List<MySqlParameter>();
            MySqlParameter param = new MySqlParameter("?cityName", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = cityName;
            dbParams.Add(param);
            try
            {
                int result = Convert.ToUInt16(SCFHS.Data.MySqlHelper.ExecuteScalar(ConStr, tbCities, CommandActions.INSERT.ToString(), dbParams.ToArray()));
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal bool UpdateCity(string cityID, string cityName)
        {
            dbParams = new List<MySqlParameter>();
            MySqlParameter param = new MySqlParameter("?cityID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = cityID;
            dbParams.Add(param);
            param = new MySqlParameter("?cityName", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = cityName;
            dbParams.Add(param);
            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbCities, CommandActions.UPDATE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal bool DeleteCity(string cityID)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                MySqlParameter param = new MySqlParameter("?cityID", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = int.Parse(cityID);
                dbParams.Add(param);
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbCities, CommandActions.DELETE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal DataTable ListCities()
        {
            DataSet ds = new DataSet();
            ds.Clear();
            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbCities, CommandActions.SELECTALL.ToString());
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #region Nationalities
        internal bool AddNewNationality(string nationalityName)
        {
            dbParams = new List<MySqlParameter>();
            MySqlParameter param = new MySqlParameter("?nationalityName", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = nationalityName;
            dbParams.Add(param);
            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbNationalities, CommandActions.INSERT.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal bool UpdateNationality(string nationalityID, string nationalityName)
        {
            dbParams = new List<MySqlParameter>();
            MySqlParameter param = new MySqlParameter("?nationalityID", MySqlDbType.Int16);
            param.Direction = ParameterDirection.Input;
            param.Value = nationalityID;
            dbParams.Add(param);
            param = new MySqlParameter("?nationalityName", MySqlDbType.VarChar);
            param.Direction = ParameterDirection.Input;
            param.Value = nationalityName;
            dbParams.Add(param);
            try
            {
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbNationalities, CommandActions.UPDATE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal bool DeleteNationality(string nationalityID)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                MySqlParameter param = new MySqlParameter("?nationalityID", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = int.Parse(nationalityID);
                dbParams.Add(param);
                int result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbNationalities, CommandActions.DELETE.ToString(), dbParams.ToArray());
                if (result >= 1)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        internal DataTable ListNationalities()
        {
            DataSet ds = new DataSet();
            try
            {
                ds = SCFHS.Data.MySqlHelper.ExecuteDataset(ConStr, tbNationalities, CommandActions.SELECTALL.ToString());
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #endregion

        #region Helper Methods
        internal Order ConvertToOrder(DataTable dt)
        {
            Order ordr = new Order();
            ordr.orderID = int.Parse(dt.Rows[0]["ID"].ToString());
            ordr.personName = dt.Rows[0]["PersonName"].ToString();
            ordr.CreationDate = Convert.ToDateTime(dt.Rows[0]["OrderCreatedOn"].ToString());
            ordr.orderNumber = dt.Rows[0]["OrderNumber"].ToString();
            ordr.orderState = (OrderStates)int.Parse(dt.Rows[0]["OrderState"].ToString());

            ordr.orderSubject = dt.Rows[0]["OrderSubject"] != null ? dt.Rows[0]["OrderSubject"].ToString() : "";
            ordr.orderClassificationNumber = dt.Rows[0]["OrderClassificationNumber"] != null ? dt.Rows[0]["OrderClassificationNumber"].ToString() : "";
            ordr.isClosed = dt.Rows[0]["IsClosed"].ToString() == "1";
            ordr.personBirthPlace = int.Parse(dt.Rows[0]["PersonBirthPlace"].ToString());
            ordr.personHighSchoolPlace = int.Parse(dt.Rows[0]["personHighSchoolPlace"].ToString());
            ordr.personIDNumber = dt.Rows[0]["PersonIDNumber"].ToString();
            ordr.personIDType = int.Parse(dt.Rows[0]["PersonIDType"].ToString());
            ordr.personNationality = int.Parse(dt.Rows[0]["PersonNationality"].ToString());
            ordr.personCurrentResidencePlace = int.Parse(dt.Rows[0]["PersonCurrentResidencePlace"].ToString());
            ordr.workPlaceName = dt.Rows[0]["PersonWorkPlace"].ToString();
            ordr.workPlaceType = int.Parse(dt.Rows[0]["PersonWorkPlaceType"].ToString());

            return ordr;
        }
        public DateTime ConvertDate(string date)
        {
            if (string.IsNullOrEmpty(date))
                return DateTime.Now;
            date = date.Replace('-', '/');
            DateTime toDate = DateTime.ParseExact(date, "dd/MM/yyyy", CultureInfo.InvariantCulture);
            return toDate;
        }

        SCFHS.Data.User ConvertToUser(DataRow dr)
        {
            SCFHS.Data.User user = new SCFHS.Data.User();
            user.userID = int.Parse(dr["Id"].ToString());
            user.userName = dr["UserName"].ToString();
            user.userPassword = dr["UserPassword"].ToString();
            user.userDefaultPage = dr["UserDefaultPage"] == null ? "" : dr["UserDefaultPage"].ToString();
            user.userProfileName = dr["UserProfileName"].ToString();
            user.isAdmin = Convert.ToBoolean(int.Parse(dr["IsAdmin"].ToString()));
            user.isActive = Convert.ToBoolean(int.Parse(dr["IsActive"].ToString()));
            user.userRole = dr["UserRole"].ToString();

            return user;
        }
        #endregion

        internal bool UpdateOrderStates(int orderID, OrderStates orderStates, int userID, bool isClosed = false)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?orderID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = orderID;
                dbParams.Add(param);

                param = new MySqlParameter("?_orderState", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = (int)orderStates;
                dbParams.Add(param);

                param = new MySqlParameter("?userID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = userID;
                dbParams.Add(param);

                param = new MySqlParameter("?_isClosed", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = Convert.ToUInt16(isClosed);
                dbParams.Add(param);

                int Result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, "", "UpdateOrderState", dbParams.ToArray());

                if (Result >= 1)
                    return true;
                return false;

            }
            catch (Exception)
            {

                throw;
            }
        }

        internal bool UpdateOrderFullInfo(int orderID, string orderSubject, string orderClassificationNumber, int orderNationality, int personBirthPlace, int personResidenceCity,
            int highSchholPlace, string personIDNumber, int personIDType, string workPlace, int workPlaceType)
        {
            dbParams = new List<MySqlParameter>();
            try
            {
                param = new MySqlParameter("?orderID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = orderID;
                dbParams.Add(param);

                param = new MySqlParameter("?_orderSubject", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = orderSubject;
                dbParams.Add(param);

                param = new MySqlParameter("?_orderClassificationNumber", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = orderClassificationNumber;
                dbParams.Add(param);

                param = new MySqlParameter("?_personNationality", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = orderNationality;
                dbParams.Add(param);

                param = new MySqlParameter("?_personBirthPlace", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = personBirthPlace;
                dbParams.Add(param);

                param = new MySqlParameter("?_personResidenceCity", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = personResidenceCity;
                dbParams.Add(param);

                param = new MySqlParameter("?_highSchoolPlace", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = highSchholPlace;
                dbParams.Add(param);

                param = new MySqlParameter("?_personIDNumber", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = personIDNumber;
                dbParams.Add(param);

                param = new MySqlParameter("?_personIDType", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = personIDType;
                dbParams.Add(param);

                param = new MySqlParameter("?_workPlace", MySqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = workPlace;
                dbParams.Add(param);

                param = new MySqlParameter("?_workPlaceType", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = workPlaceType;
                dbParams.Add(param);

                param = new MySqlParameter("?_userID", MySqlDbType.Int16);
                param.Direction = ParameterDirection.Input;
                param.Value = ((Data.User)HttpContext.Current.User).userID;
                dbParams.Add(param);

                int Result = SCFHS.Data.MySqlHelper.ExecuteNonQuery(ConStr, tbOrder, CommandActions.UPDATE.ToString(), dbParams.ToArray());

                if (Result >= 1)
                    return true;
                return false;

            }
            catch (Exception)
            {

                throw;
            }
        }
    }
}